use master
GO
--Drop/Create Database
drop database CafeDB
GO
create database CAFEDB
on(name='CAFEDB_DATA' ,filename='F:\Project on tfs codeplex\sokulsoftware\DB\CAFEDB.mdf')
log on(name='CAFEDB_LOG' ,filename='F:\Project on tfs codeplex\sokulsoftware\DB\CAFEDB.ldf')
GO
USE CAFEDB
GO
--Create Table

CREATE table AREA
(
	AREAID VARCHAR (20),
	AREANAME NVARCHAR(50) not null
	constraint PK_AREA primary key(AREAID)
)
go

--Ban
create table  [TABLE]
(
	TABLEID INT IDENTITY(1,1) not null,
	TABLENAME NVARCHAR(50)not null,
	AREAID VARCHAR (20) not null
	constraint PK_TABLE primary key(TABLEID),
	constraint FK_AREA_TABLE foreign key(AREAID)
	references AREA(AREAID)
	on update cascade
)
GO

create table DEPARTMENT
(
	DEPARTMENTID varchar(20)not null,
	DEPARTMENTNAME nvarchar(100)not null
	constraint PK_DEPARTMENT primary key(DEPARTMENTID)
)
go

--BacLuong
create table WAGE
(
	WAGEID INT IDENTITY(1,1) not null,
	WAGENAME nvarchar(50) not null,
	PRICE float,
	DEPARTMENTID varchar(20) not null
	constraint PK_WAGE primary key(WAGEID,DEPARTMENTID),
	constraint FK_WAGE_DEPARTMENT foreign key (DEPARTMENTID)
	references DEPARTMENT(DEPARTMENTID)
	on update cascade
)
go

--Nhanvien
create table EMPLOYER
(
	EMPLOYERID int identity(1,1) not null,
	FIRSTNAME NVARCHAR(50) NOT NULL,
	MIDDLENAME NVARCHAR(50) NOT NULL,
	LASTNAME NVARCHAR(50) NOT NULL,
	FULLNAME NVARCHAR(150),
	DOB DATETIME NOT NULL,
	IDCARD varchar(9)not null,
	[ADDRESS] nvarchar(100),
	DATEWORK datetime not null,
	DEPARTMENTID varchar(20) not null
	constraint PK_EMPLOYER primary key(EMPLOYERID),
	constraint FK_EMPLOYER_DEPARTMENT foreign KEY (DEPARTMENTID)
	references DEPARTMENT(DEPARTMENTID)
	on update cascade
)
GO
CREATE TABLE [GROUPROLE]
(
	GROUPROLECODE VARCHAR(50) PRIMARY KEY NOT NULL,
	GROUPROLENAME NVARCHAR(100) NOT NULL,
	USERCREATE VARCHAR(30),
	DATECREATE DATETIME,
	USERUPDATE VARCHAR(30),
	LASTUPDATE DATETIME ,
)
GO

CREATE TABLE [ROLE]
(
	ROLECODE VARCHAR(50) PRIMARY KEY NOT NULL,
	ROLENAME NVARCHAR(100) NOT NULL,
	GROUPROLECODE VARCHAR(50) NOT NULL,
	USERCREATE VARCHAR(30),
	DATECREATE DATETIME,
	USERUPDATE VARCHAR(30),
	LASTUPDATE DATETIME ,
)
GO

CREATE TABLE GROUPUSER
(
	GROUPUSERCODE INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	GROUPUSERNAME NVARCHAR(100) NOT NULL,
	GROUPUSERDESCRIPTION NVARCHAR(100),
	USERCREATE VARCHAR(30),
	DATECREATE DATETIME,
	USERUPDATE VARCHAR(30),
	LASTUPDATE DATETIME ,
)
GO
CREATE TABLE DETAIL_GROUPUSER_ROLE
(
	GROUPUSERCODE INT NOT NULL,
	ROLECODE VARCHAR(50) NOT NULL,
	PERMIT BIT 
	CONSTRAINT PK_DETAIL_GROUPUSER_ROLE PRIMARY KEY(GROUPUSERCODE,ROLECODE),
	CONSTRAINT FK_DETAIL_GROUPUSER_ROLE_GROUPUSER FOREIGN KEY(GROUPUSERCODE)
	REFERENCES GROUPUSER(GROUPUSERCODE),
	CONSTRAINT FK_DETAIL_GROUPUSER_ROLE_TOLE FOREIGN KEY(ROLECODE)
	REFERENCES [ROLE](ROLECODE)
)
GO
CREATE TABLE USERACCOUNT
(
	USERNAME VARCHAR(30) NOT NULL PRIMARY KEY ,
	[PASSWORD] VARCHAR(32) NOT NULL,
	FIRSTNAME NVARCHAR(50) NOT NULL,
	MIDDLENAME NVARCHAR(50) NOT NULL,
	LASTNAME NVARCHAR(50) NOT NULL,
	FULLNAME NVARCHAR(150),
	DOB DATETIME NOT NULL,
	EMAIL VARCHAR(100) NOT NULL,
	IDCARD VARCHAR(20) NOT NULL,
	DATECREATE DATETIME NOT NULL,
	LASTUPDATE DATETIME,
	ISADMIN BIT NOT NULL,
	MOBILEPHONE VARCHAR(20),
	HOMEPHONE VARCHAR(20),
	GROUPUSERCODE INT NOT NULL,
	EMPLOYERID varchar(20)
	CONSTRAINT FK_USERACCOUNT_GROUPUSER FOREIGN KEY(GROUPUSERCODE)
	REFERENCES GROUPUSER(GROUPUSERCODE)
	
)
GO

CREATE TABLE ITEMTYPE
(
	ITEMTYPEID int identity(1,1)not null,
	ITEMTYPENAME nvarchar(50)
	constraint PK_PhanLoaiSanPham primary key(ITEMTYPEID)
)
go
--Sanpham
CREATE TABLE ITEM 
(
	ITEMID int identity(1,1) not null,
	ITEMNAME nvarchar(50),
	ITEMTYPEID int not null,
	ISDRINK bit not null,
	PRICE float not null,
	SHORTCUT VARCHAR(50)
	constraint PK_ITEM primary key(ITEMID),
	constraint FK_ITEM_ITEMTYPE foreign key(ITEMTYPEID)
	references ITEMTYPE(ITEMTYPEID)
	on update cascade
)
go
--Donvi
CREATE TABLE UNIT
(
	UNITID int identity(1,1) not null,
	UNITNAME nvarchar(50) not null
	constraint PK_UNIT primary key(UNITID)
)
go
--nguyenLieu
CREATE TABLE MATERIAL
(
	MATERIALID int identity(1,1) not null,
	MATERIALNAME nvarchar(50) not null,
	QUANTITY float,
	UNITID int not null
	constraint PK_MATERIAL primary key(MATERIALID)
	constraint FK_MATERIAL foreign key(UNITID)
	references UNIT(UNITID)
)
go

--HoaDonNhap
CREATE TABLE IMPORT
(
	IMPORTID varchar(10) not null,
	DATEIMPORT datetime not null,
	ISDEBT bit not null,
	PAID float,
	NOTE nvarchar(500),
	EMPLOYERID int  not null
	constraint PK_IMPORT primary key(IMPORTID),
	constraint FK_IMPORT_EMPLOYER foreign key(EMPLOYERID)
	references EMPLOYER(EMPLOYERID)
)
go
--CThoadonnhap
CREATE TABLE IMPORTDETAIL
(
	IMPORTID varchar(10) not null,
	MATERIALID int not null,
	QUANTITY float not null,
	UNITID int not null
	constraint PK_IMPORTDETAIL primary key(IMPORTID,MATERIALID),
	constraint FK_IMPORTDETAIL_IMPORT foreign key(IMPORTID)
	references IMPORT(IMPORTID),
	constraint FK_IMPORTDETAIL_MATERIAL foreign key(MATERIALID)
	references MATERIAL(MATERIALID)

)
go
--Hoadonxuat
CREATE TABLE EXPORT
(
	EXPORTID varchar(10) not null,
	TABLEID INT not null,
	EMPLOYERID int not null,
	TABLEDISCOUNT float,
	DATECREATE DATETIME,
	ISPAYALL bit,
	VAT float,
	[SERVICE] float,
	DATEPAID DATETIME
	constraint PK_EXPORT primary key(EXPORTID),
	constraint FK_EXPORT_TABLE foreign key(TABLEID)
	references [TABLE](TABLEID),
	constraint FK_EXPORT_EMPLOYER foreign key(EMPLOYERID)
	references EMPLOYER(EMPLOYERID)
)
go
--CThoadonxuat
create table EXPORTDETAIL
(
	EXPORTID varchar(10) not null,
	ITEMID int not null,
	QUANTITY int not null,
	PRICE float,
	ITEMDISCOUNT float,
	ISITEMPAY bit
	constraint PK_EXPORTDETAIL primary key(EXPORTID,ITEMID),
	constraint FK_EXPORTDETAIL_ITEM foreign key(ITEMID)
	references ITEM(ITEMID),
	constraint FK_EXPORTDETAIL_EXPORT foreign key(EXPORTID)
	references EXPORT(EXPORTID)
)
go


DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime